CREATE TABLE AssetGroup ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, Name VARCHAR(200) NOT NULL ) GO CREATE TABLE MeterAssetGroup ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, MeterID INT NOT NULL REFERENCES Meter(ID), AssetGroupID INT NOT NULL REFERENCES AssetGroup(ID) ) GO CREATE NONCLUSTERED INDEX IX_MeterAssetGroup_MeterID ON MeterAssetGroup(MeterID ASC) GO CREATE NONCLUSTERED INDEX IX_MeterAssetGroup_AssetGroupID ON MeterAssetGroup(AssetGroupID ASC) GO CREATE TABLE LineAssetGroup ( ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, LineID INT NOT NULL REFERENCES Line(ID), AssetGroupID INT NOT NULL REFERENCES AssetGroup(ID), ) GO CREATE NONCLUSTERED INDEX IX_LineAssetGroup_LineID ON LineAssetGroup(LineID ASC) GO CREATE NONCLUSTERED INDEX IX_LineAssetGroup_AssetGroupID ON LineAssetGroup(AssetGroupID ASC) GO INSERT INTO AssetGroup(Name) VALUES('AllAssets') GO CREATE TRIGGER Meter_AugmentAllAssetsGroup ON Meter AFTER INSERT AS BEGIN SET NOCOUNT ON; INSERT INTO MeterAssetGroup(MeterID, AssetGroupID) SELECT Meter.ID, AssetGroup.ID FROM inserted Meter CROSS JOIN AssetGroup WHERE AssetGroup.Name = 'AllAssets' END GO CREATE TRIGGER Line_AugmentAllAssetsGroup ON Line AFTER INSERT AS BEGIN SET NOCOUNT ON; INSERT INTO LineAssetGroup(LineID, AssetGroupID) SELECT Line.ID, AssetGroup.ID FROM inserted Line CROSS JOIN AssetGroup WHERE AssetGroup.Name = 'AllAssets' END GO ALTER TABLE UserAccount ADD UNIQUE(Name) GO ALTER TABLE UserAccount ADD PhoneConfirmed BIT NOT NULL DEFAULT 0 GO ALTER TABLE UserAccount ADD EmailConfirmed BIT NOT NULL DEFAULT 0 GO ALTER TABLE UserAccount ADD Approved BIT NOT NULL DEFAULT 0 GO CREATE TABLE UserAccountAssetGroup ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, UserAccountID UNIQUEIDENTIFIER NOT NULL REFERENCES UserAccount(ID), AssetGroupID INT NOT NULL REFERENCES AssetGroup(ID), Dashboard BIT NOT NULL DEFAULT 1, Email BIT NOT NULL DEFAULT 0 ) GO CREATE TRIGGER UserAccount_AugmentAllAssetsGroup ON UserAccount AFTER INSERT AS BEGIN SET NOCOUNT ON; INSERT INTO UserAccountAssetGroup(UserAccountID, AssetGroupID) SELECT UserAccount.ID, AssetGroup.ID FROM inserted UserAccount CROSS JOIN AssetGroup WHERE AssetGroup.Name = 'AllAssets' END GO ALTER TABLE XSLTemplate ADD UNIQUE(Name) GO ALTER TABLE EmailType ADD SMS BIT NOT NULL DEFAULT 0 GO CREATE TABLE EventEmailParameters ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, EmailTypeID INT NOT NULL UNIQUE REFERENCES EmailType(ID), TriggersEmailSQL VARCHAR(MAX) NOT NULL DEFAULT 'SELECT 0', EventDetailSQL VARCHAR(MAX) NOT NULL DEFAULT 'SELECT '''' FOR XML PATH(''EventDetail''), TYPE', MinDelay FLOAT NOT NULL DEFAULT 10, MaxDelay FLOAT NOT NULL DEFAULT 60 ) GO CREATE TABLE UserAccountEmailType ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, UserAccountID UNIQUEIDENTIFIER NOT NULL REFERENCES UserAccount(ID), EmailTypeID INT NOT NULL REFERENCES EmailType(ID) ) GO INSERT INTO EventEmailParameters(EmailTypeID) VALUES(1) GO ALTER FUNCTION ComputeHash ( @eventID INT, @templateID INT ) RETURNS BIGINT BEGIN DECLARE @md5Hash BINARY(16) DECLARE @eventDetailSQL VARCHAR(MAX) = ( SELECT EventDetailSQL FROM EmailType JOIN EventEmailParameters ON EventEmailParameters.EmailTypeID = EmailType.ID WHERE EmailType.XSLTemplateID = @templateID ) DECLARE @eventDetail VARCHAR(MAX) EXEC sp_executesql @eventDetailSQL, @eventID, @eventDetail OUT SELECT @md5Hash = master.sys.fn_repl_hash_binary(CONVERT(VARBINARY(MAX), @eventDetail)) FROM EventDetail WHERE EventID = @eventID SELECT @md5Hash = master.sys.fn_repl_hash_binary(@md5Hash + CONVERT(VARBINARY(MAX), Template)) FROM XSLTemplate WHERE ID = @templateID RETURN CONVERT(BIGINT, SUBSTRING(@md5Hash, 0, 8)) ^ CONVERT(BIGINT, SUBSTRING(@md5Hash, 8, 8)) END GO CREATE VIEW MeterAssetGroupView AS SELECT MeterAssetGroup.ID, Meter.Name AS MeterName, Meter.ID AS MeterID, AssetGroupID, MeterLocation.Name AS Location FROM MeterAssetGroup JOIN Meter ON MeterAssetGroup.MeterID = Meter.ID JOIN MeterLocation ON Meter.MeterLocationID = MeterLocation.ID GO CREATE VIEW LineAssetGroupView AS SELECT LineAssetGroup.ID, Line.AssetKey AS LineName, (SELECT TOP 1 LineName FROM MeterLine Where LineID = Line.ID) AS LongLineName, Line.ID AS LineID, AssetGroupID FROM LineAssetGroup JOIN Line ON LineAssetGroup.LineID = Line.ID GO CREATE VIEW UserAccountAssetGroupView AS SELECT UserAccountAssetGroup.ID, UserAccountAssetGroup.UserAccountID, UserAccountAssetGroup.AssetGroupID, UserAccountAssetGroup.Dashboard, UserAccountAssetGroup.Email, UserAccount.Name AS Username, AssetGroup.Name AS GroupName FROM UserAccountAssetGroup JOIN UserAccount ON UserAccountAssetGroup.UserAccountID = UserAccount.ID JOIN AssetGroup ON UserAccountAssetGroup.AssetGroupID = AssetGroup.ID GO ALTER VIEW UserMeter AS SELECT DISTINCT UserAccount.Name AS UserName, Meter.ID AS MeterID FROM UserAccount JOIN UserAccountAssetGroup ON UserAccountAssetGroup.UserAccountID = UserAccount.ID LEFT OUTER JOIN MeterAssetGroup ON MeterAssetGroup.AssetGroupID = UserAccountAssetGroup.AssetGroupID LEFT OUTER JOIN LineAssetGroup ON LineAssetGroup.AssetGroupID = UserAccountAssetGroup.AssetGroupID LEFT OUTER JOIN MeterLine ON MeterLine.LineID = LineAssetGroup.LineID JOIN Meter ON MeterAssetGroup.MeterID = Meter.ID OR MeterLine.MeterID = Meter.ID WHERE UserAccount.Approved <> 0 AND UserAccountAssetGroup.Dashboard <> 0 GO UPDATE EventEmailParameters SET TriggersEmailSQL = 'SELECT CASE WHEN EventType.Name = ''Fault'' THEN 1 ELSE 0 END FROM Event JOIN EventType ON Event.EventTypeID = EventType.ID WHERE Event.ID = {0}' WHERE EventEmailParameters.ID = 1 GO UPDATE EventEmailParameters SET EventDetailSQL = 'DECLARE @timeTolerance FLOAT = (SELECT CAST(Value AS FLOAT) FROM Setting WHERE Name = ''TimeTolerance'') DECLARE @lineID INT DECLARE @startTime DATETIME2 DECLARE @endTime DATETIME2 SELECT @lineID = LineID, @startTime = dbo.AdjustDateTime2(StartTime, -@timeTolerance), @endTime = dbo.AdjustDateTime2(EndTime, @timeTolerance) FROM Event WHERE ID = {0} SELECT * INTO #lineEvent FROM Event WHERE Event.LineID = @lineID AND Event.EndTime >= @startTime AND Event.StartTime <= @endTime SELECT ROW_NUMBER() OVER(PARTITION BY Event.MeterID ORDER BY FaultSummary.Inception) AS FaultNumber, FaultSummary.ID AS FaultSummaryID, Meter.AssetKey AS MeterKey, Meter.Make AS MeterMake, Meter.Name AS MeterName, MeterLocation.AssetKey as StationKey, MeterLocation.Name AS StationName, Line.AssetKey AS LineKey, MeterLine.LineName, MeterLine.BreakerName, FaultSummary.FaultType, FaultSummary.Inception, FaultSummary.DurationCycles, FaultSummary.DurationSeconds * 1000.0 AS DurationMilliseconds, FaultSummary.PrefaultCurrent, FaultSummary.PostfaultCurrent, FaultSummary.ReactanceRatio, FaultSummary.CurrentMagnitude AS FaultCurrent, FaultSummary.Algorithm, FaultSummary.Distance AS SingleEndedDistance, DoubleEndedFaultSummary.Distance AS DoubleEndedDistance, DoubleEndedFaultSummary.Angle AS DoubleEndedAngle, RIGHT(DataFile.FilePath, CHARINDEX(''\'', REVERSE(DataFile.FilePath)) - 1) AS FileName, FaultSummary.EventID, Event.StartTime AS EventStartTime, SimpleSummary.Distance AS Simple, ReactanceSummary.Distance AS Reactance, Event.EndTime AS EventEndTime INTO #summaryData FROM #lineEvent Event JOIN FaultSummary ON FaultSummary.EventID = Event.ID AND FaultSummary.IsSelectedAlgorithm <> 0 AND FaultSummary.IsSuppressed = 0 LEFT OUTER JOIN FaultSummary SimpleSummary ON FaultSummary.EventID = SimpleSummary.EventID AND FaultSummary.Inception = SimpleSummary.Inception AND SimpleSummary.Algorithm = ''Simple'' LEFT OUTER JOIN FaultSummary ReactanceSummary ON FaultSummary.EventID = ReactanceSummary.EventID AND FaultSummary.Inception = ReactanceSummary.Inception AND ReactanceSummary.Algorithm = ''Reactance'' JOIN DataFile ON DataFile.FileGroupID = Event.FileGroupID JOIN Meter ON Event.MeterID = Meter.ID JOIN MeterLocation ON Meter.MeterLocationID = MeterLocation.ID JOIN MeterLine ON MeterLine.MeterID = Meter.ID AND MeterLine.LineID = Event.LineID JOIN Line ON Line.ID=MeterLine.LineID LEFT OUTER JOIN DoubleEndedFaultDistance ON DoubleEndedFaultDistance.LocalFaultSummaryID = FaultSummary.ID LEFT OUTER JOIN DoubleEndedFaultSummary ON DoubleEndedFaultSummary.ID = DoubleEndedFaultDistance.ID WHERE DataFile.FilePath LIKE ''%.DAT'' OR DataFile.FilePath LIKE ''%.D00'' OR DataFile.FilePath LIKE ''%.PQD'' OR DataFile.FilePath LIKE ''%.RCD'' OR DataFile.FilePath LIKE ''%.RCL'' OR DataFile.FilePath LIKE ''%.SEL'' OR DataFile.FilePath LIKE ''%.EVE'' OR DataFile.FilePath LIKE ''%.CEV'' DECLARE @url VARCHAR(MAX) = (SELECT Value FROM DashSettings WHERE Name = ''System.URL'') SELECT ( SELECT ID AS [@id] FROM #lineEvent FOR XML PATH(''Event''), TYPE ) AS [Events], ( SELECT FaultNumber AS [@num], ( SELECT MeterKey, MeterName, StationKey, StationName, LineKey, LineName, FaultType, Inception, DurationCycles, DurationMilliseconds, PrefaultCurrent, PostfaultCurrent, ReactanceRatio, FaultCurrent, Algorithm, SingleEndedDistance, DoubleEndedDistance, DoubleEndedAngle, EventStartTime, EventEndTime, FileName, SUBSTRING(FileName, LEN(Filename) - 7, 8) AS ShortFileName, BreakerName, EventID, FaultSummaryID AS FaultID, CASE WHEN ABS(Reactance/COALESCE(Simple,1)) > 0.6 THEN ''LOW'' WHEN ABS(Reactance/COALESCE(Simple,1)) < 0.4 THEN ''HIGH'' ELSE ''MEDIUM'' END AS Ratio FROM #summaryData WHERE FaultNumber = Fault.FaultNumber FOR XML PATH(''SummaryData''), TYPE ) FROM ( SELECT DISTINCT FaultNumber FROM #summaryData ) Fault FOR XML PATH(''Fault''), TYPE ) AS [Faults], MeterLine.LineName AS [Line/Name], Line.AssetKey AS [Line/AssetKey], FORMAT(Line.Length, ''0.##########'') AS [Line/Length], FORMAT(SQRT(LineImpedance.R1 * LineImpedance.R1 + LineImpedance.X1 * LineImpedance.X1), ''0.##########'') AS [Line/Z1], CASE LineImpedance.R1 WHEN 0 THEN ''0'' ELSE FORMAT(ATN2(LineImpedance.X1, LineImpedance.R1) * 180 / PI(), ''0.##########'') END AS [Line/A1], FORMAT(LineImpedance.R1, ''0.##########'') AS [Line/R1], FORMAT(LineImpedance.X1, ''0.##########'') AS [Line/X1], FORMAT(SQRT(LineImpedance.R0 * LineImpedance.R0 + LineImpedance.X0 * LineImpedance.X0), ''0.##########'') AS [Line/Z0], CASE LineImpedance.R0 WHEN 0 THEN ''0'' ELSE FORMAT(ATN2(LineImpedance.X0, LineImpedance.R0) * 180 / PI(), ''0.##########'') END AS [Line/A0], FORMAT(LineImpedance.R0, ''0.##########'') AS [Line/R0], FORMAT(LineImpedance.X0, ''0.##########'') AS [Line/X0], FORMAT(SQRT(POWER((2.0 * LineImpedance.R1 + LineImpedance.R0) / 3.0, 2) + POWER((2.0 * LineImpedance.X1 + LineImpedance.X0) / 3.0, 2)), ''0.##########'') AS [Line/ZS], CASE 2.0 * LineImpedance.R1 + LineImpedance.R0 WHEN 0 THEN ''0'' ELSE FORMAT(ATN2((2.0 * LineImpedance.X1 + LineImpedance.X0) / 3.0, (2.0 * LineImpedance.R1 + LineImpedance.R0) / 3.0) * 180 / PI(), ''0.##########'') END AS [Line/AS], FORMAT((2.0 * LineImpedance.R1 + LineImpedance.R0) / 3.0, ''0.##########'') AS [Line/RS], FORMAT((2.0 * LineImpedance.X1 + LineImpedance.X0) / 3.0, ''0.##########'') AS [Line/XS], @url AS [PQDashboard] FROM Event JOIN Line ON Event.LineID = Line.ID JOIN MeterLine ON MeterLine.MeterID = Event.MeterID AND MeterLine.LineID = Event.LineID JOIN LineImpedance ON LineImpedance.LineID = Line.ID WHERE Event.ID = {0} FOR XML PATH(''EventDetail''), TYPE' WHERE EventEmailParameters.ID = 1 GO INSERT INTO Setting(Name, Value, DefaultValue) VALUES('Email.ApprovalAddress', 'xda-admin@gridprotectionalliance.org', 'xda-admin@gridprotectionalliance.org') GO INSERT INTO Setting(Name, Value, DefaultValue) VALUES('EventEmail.Enabled', 'False', 'False') GO INSERT INTO DashSettings (Name, Value, Enabled) VALUES ('System.URL', 'http://localhost/PQDashboard', 1) GO